﻿Create database Student01
on
(
fileName='D:\homework\Student01.mdf',
Name='Student01',
Size=1MB,
Maxsize=5MB,
filegrowth=1MB
)
log on
(
fileName='D:\homework\Student01_log.ldf',
Name='Student01_log.ldf',
size=1MB,
Maxsize=5MB,
filegrowth=1MB
)
go

use Students
go

create table ClassInfo
(
ClassID int primary key identity(1,1),
ClassName nvarchar(20) unique not null
)

create table Student01
(
StuID int primary key identity(1,1),
ClassID int foreign key references ClassInfo(ClassID),
StuName nvarchar not null,
StuSex nvarchar(1) check(StuSex='' or StuSex='Ů'),
StuBirthday date,
StuPhone nvarchar unique,
)


create table Course
(
CourseID int primary key identity(1,1),
CourseName nvarchar unique not null,
CourseCredit int default(1) check(CourseCredit>=1 or CourseCredit<=5),
)


create table Score
(
ScoreID int identity(1,1),
StuID int,
CourseID int ,
Score decimal(5,2) unique not null
)
--
alter table Student01 add StuAddress nvarchar
--
alter table Score add constraint PK_Score_ScoreID primary key(ScoreID)
--
alter table Score add constraint FK_Score_StuID foreign key(StuID) references Student(StuID)
--
alter table Score add constraint FK_Score_CourseID foreign key(CourseID) references Course(CourseID)